#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@Date:2022/07/19 10:49:15
'''

import sys
sys.path.append('.')
from decimal import Decimal
from db_logic.db_base import Repository

from models.medicament_models import EntityMedicamentRecord

from Common.Utils import Utils
# from common.utils import Utils

#药剂流转记录业务逻辑类
class BllMedicamentRecord(Repository):
    #_instance_lock = threading.Lock()
    ##实现单例模式
    #def __new__(cls, *args, **kwargs):
    #    if not hasattr(BllMedicamentRecord, "_instance"):
    #        with BllMedicamentRecord._instance_lock:
    #            if not hasattr(BllMedicamentRecord, "_instance"):
    #                BllMedicamentRecord._instance = object.__new__(cls)
    #    return BllMedicamentRecord._instance

    def __init__(self, entityType=EntityMedicamentRecord):
        return super().__init__(entityType)


    # 获取流转记录
    def get_drug_flow_log(self, medicament_id, func_type, page_param):
        filter_base = ""
        if medicament_id:
            filter_base += f" medicament_id='{medicament_id}' "
        # if func_type:
        #     if filter_base:
        #         filter_base += " and "
        #     filter_base += f" func_type='{func_type}' "
        if filter_base:
            filter_base = f" where {filter_base}"
        sql_all = f"""
        
        select  a.*, b.create_date, b.create_user_name from(
            select medicament_id, create_date, create_user_id, create_user_name from rms_medicament_record {filter_base}
	    ) b LEFT JOIN(
            select * from rms_medicament {filter_base}
        ) a on b.medicament_id=a.medicament_id order by create_date desc
        """
        # count_sql = "select count(*) from rms_medicament_record"
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        page_param.totalRecords = count_number
        sql_all = Utils.sql_paging_assemble(sql_all, page_param)
        return self.execute(sql_all).fetchall()


    #获取今日药剂流转信息数量
    def getTodayDrugRecordCount(self, func_type):
        sql_all = f"""
        SELECT 
        record_type, count(record_id) type_number 
        FROM rms_medicament_record as a LEFT JOIN rms_medicament as b on a.medicament_id=b.medicament_id
        where TO_DAYS(create_date)=TO_DAYS('{Utils.get_str_datetime()}') and func_type='{func_type}'
        GROUP BY record_type 

        """
        sql_data = self.execute(sql_all).fetchall()
        n_c = self.execute(f"select count(*) num from rms_medicament where `status`= 2 and func_type={func_type}").fetchone().num
        data_list = []
        default_list = [(1,0),(2,0),(3,0)]
        sql_data = sql_data if sql_data else default_list
        for i in sql_data:
            data_list.append({
                "record_type":i[0],
                "type_number":i[1]
            })
        data_list.append(
            {
                "record_type":100,
                "type_number": n_c
            }
        )
        return data_list

    def get_drug_log_time_name(self, record_type_list):
        sql_all = """
            select a.`name` `name`,a.medicament_id, b.create_date create_date, b.record_type from (
                select `name`, medicament_id from rms_medicament
                ) a RIGHT JOIN(
                    select medicament_id, create_date, record_type from rms_medicament_record WHERE record_type=%s ORDER BY create_date desc LIMIT 1
                    ) b on a.medicament_id=b.medicament_id 
            """
        sql_list = []
        for record_type in record_type_list:
            sql_list.append(
                sql_all % record_type
            )
        sql_all = " UNION ".join(sql_list)
        # print(sql_all)
        return self.execute(sql_all).fetchall()
    
    # 获取报表统计主页，使用频率
    def get_use_avg_number(self, func_type):
        sql_all = f"""
        select a.`name`, IFNULL(b.count_number ,0) count_number from ( 
        select medicament_id, purity, `name`, speci from rms_medicament where func_type='{func_type}'
        ) a LEFT JOIN(
        select medicament_id, count(record_id) count_number from rms_medicament_record  where record_type=2 GROUP BY medicament_id
        ) b
        on a.medicament_id=b.medicament_id GROUP BY a.purity, a.`name`, a.speci ORDER BY count_number desc 
        """
        sql_data = self.execute(sql_all).fetchall()
        if not sql_data:
            resp_data = {
                "avg_use_number": 0,
                "max_name": "",
                "max_number": 0
            }
        else:
            use_number = sum([float(i[1]) for i in sql_data])
            drug_num = len(sql_data)
            
            resp_data = {
                "avg_use_number": round(use_number / drug_num),
                "max_name": sql_data[0][0],
                "max_number": sql_data[0][1]
            }
        return resp_data

    # 获取报表统计主页入库记录信息
    def get_drug_record_count(self, func_type):
        # select COUNT(record_id) count_number, record_type from rms_medicament_record GROUP BY record_type
        sql_all = f"""
            select count(record_id) count_number, record_type from (
                select record_id,medicament_id, record_type from rms_medicament_record
            ) as a LEFT JOIN
                rms_medicament as b on a.medicament_id=b.medicament_id where func_type='{func_type}'
            GROUP BY record_type 
        """
        # 获取分类数量
        data = self.execute(sql_all).fetchall()
        data_li = Utils.msyql_table_model(data)
        data_list = []
        # 获取时间维度最后一条名称及时间
        name_info_list = self.get_drug_log_time_name([1,2,3])
        name_info_dict = {}
        for name in name_info_list:
            name_info_dict[name[-1]] = name
        # 组装数据  
        type_num = []   
        for i in data_li:
            name_info = name_info_dict.get(i['record_type'])
            new_dic = {
                "name": name_info[0],
                "date_time": name_info[2]
            }
            new_dic.update(**i)
            type_num.append(str(i["record_type"]))
            data_list.append(new_dic)
        set_num_li = list(set(["1", "2", "3"]).difference(set(type_num)))
        for i in set_num_li:
            data_list.append(
                {
                    "record_type":int(i),
                    "name":"",
                    "date_time":""
                }
            )
        return data_list, self.get_use_avg_number(func_type)

    # 获取报表统计主页试剂用量消耗
    def report_home_drug_useing_classify(self, func_type):
        sql_all = f"""
            select a.`name`, IFNULL(sum(b.count_id),0) count_num from (
            select `name`, medicament_id, speci, purity from rms_medicament where func_type='{func_type}') a LEFT JOIN(
                select count(record_id) count_id, medicament_id from rms_medicament_record GROUP BY medicament_id
            )b on a.medicament_id=b.medicament_id GROUP BY `name`, speci, purity
        """
        data = self.execute(sql_all).fetchall()
        data_number = []
        for i in data:
            if i[1]:
                data_number.append(float(i[1]))
            else:
                data_number.append(0)
        # data_number = sum([float(i[1]) if i[1] else 0 for i in data])
        data_number = sum(data_number)
        data_li = Utils.msyql_table_model(data)
        data_list = []
        for i in data_li:
            if data_number == 0:
                ratio = 0
            else:
                ratio = Utils.classify(i["count_num"], data_number)
            new_dic = {
                "ratio": ratio
            }
            if i["count_num"] == 0:
                count_num = ''
            else:
                count_num = int(i["count_num"])
            i["count_num"] = count_num
            new_dic.update(**i)
            data_list.append(new_dic)
        return data_list
    
    # 获取报表统计主页人员用量消耗
    def report_home_user_use_info(self, func_type):
        sql_all = f"""
            select a.count_id count_number, a.create_user_id, a.create_user_name, b.avatar_url from(
                    select count(record_id) count_id, create_user_id,create_user_name  from 
                        rms_medicament_record as c 
                            LEFT JOIN 
                                rms_medicament as d
                    on c.medicament_id=d.medicament_id
                    where func_type='{func_type}' 
                    GROUP BY create_user_id
            ) a LEFT JOIN(
                select avatar_url, user_id from rms_user
            ) b on a.create_user_id= b.user_id
            order by count_number desc

        """
        data = self.execute(sql_all).fetchall()
        data_list = Utils.msyql_table_model(data)
        return data_list

    # 公用方法
    def default_data_list(self, sql_all, finds=None):
        """
        参数介绍
        sql_all = 最终执行sql
        finds = 需要聚合的字段
        """
        # 获取数据
        med_data = self.execute(sql_all).fetchall()
        data_list = []
        base_num = 0
        # 数据格式化
        data= Utils.msyql_table_model(med_data)
        # 数据处理，转换float类型，聚合字段，返回新列表
        for d in data:
            new_dic = {}
            for k,v in d.items():
                new_dic[k] = v if not isinstance(v, Decimal) else float(v)
            data_list.append(new_dic)
            if finds:
                try:
                    finds_num = float(new_dic.get(finds))
                except:
                    finds_num = 0
                base_num += finds_num
        return data_list, base_num

    # 获取库存消耗数据
    def durg_stock_loss(self, seach_word,  func_type, page_param):
        filter_base = ""
        if seach_word:
            seach_word = f"%{seach_word}%"
            filter_base += f" (`name` like '{seach_word}' or english_name like '{seach_word}') "
        if func_type:
            if filter_base:
                filter_base += " and "
            filter_base += f" func_type='{func_type}'"
        if filter_base:
            filter_base = f" where {filter_base}"
        sql_all = f"""
        select `name`, cas_number,speci, net_weight_unit, purity, enter_stock,
					sum(CASE WHEN `status`=1 THEN 1 ELSE 0 END) in_stock,
					sum(CASE WHEN `status`=2 THEN 1 ELSE 0 END) up_stock,
					sum(CASE WHEN `status`=3 THEN 1 ELSE 0 END) no_stock 
		from (
				select medicament_id, `name`, english_name,cas_number, speci, purity, net_weight_unit, `status`, func_type from rms_medicament
		) a LEFT JOIN(
				SELECT medicament_id, count(medicament_id) enter_stock, create_date  from rms_medicament_record where record_type=1  GROUP BY medicament_id
		) b on b.medicament_id = a.medicament_id
            {filter_base}
		
		GROUP BY `name`, purity, speci

        """
        # 查询获取数据条目数
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        if page_param:
            page_param.totalRecords = count_number
            # 数据分页处理
            sql_all = Utils.sql_paging_assemble(sql_all, page_param)
        # 获取处理后的数据
        data_list, _ = self.default_data_list(sql_all)
        return data_list

    # 试剂用量消耗
    def durg_useing_info(self, seach_word, func_type, page_param):
        # select medicament_id, FORMAT(sum(use_quantity),2) sum_use, sum(CASE WHEN record_type=2 THEN 1 ELSE 0 END) c_two from rms_medicament_record %s GROUP BY medicament_id
        filter_base = ""
        if seach_word:
            seach_word = f"%{seach_word}%"
            filter_base += f" (`name` like '{seach_word}' or english_name like '{seach_word}') "
        if func_type:
            if filter_base:
                filter_base += " and "
            filter_base += f" func_type='{func_type}'"
        if filter_base:
            filter_base = f" where {filter_base}"
        sql_all = f"""
        select `name`, purity, speci, cas_number, net_weight_unit, 
			 sum(CASE WHEN `status`=1 THEN 1 ELSE 0 END) on_in, 
			 IFNULL(c_two,0) use_num, IFNULL(sum_use,0) use_level  from (
        select medicament_id, `name`, english_name, speci, cas_number, purity, net_weight_unit,put_in_date, `status`,func_type from rms_medicament  
        )as a LEFT JOIN(
			 select medicament_id, FORMAT(sum(use_quantity),2) sum_use, count(medicament_id) c_two from rms_medicament_record where record_type=2 GROUP BY medicament_id
			 ) b on a.medicament_id=b.medicament_id  {filter_base} GROUP BY `name`, purity, speci order by use_level desc
        """

        # 获取数量
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        if page_param:
            page_param.totalRecords = count_number
            sql_all = Utils.sql_paging_assemble(sql_all, page_param)
        # 获取组建后的数据结果
        data, ratio_all = self.default_data_list(sql_all, "use_level")
        # 拼接百分比数据
        for i in data:
            try:
                i["ratio"] = Utils.classify(i["use_level"], ratio_all)
            except Exception:
                i["ratio"] = "0%"
        return data

    # 人员用量消耗
    def user_use_info(self, page_param, start_time, end_time, func_type, seach_user=None):
        filter_base = ""
        if seach_user:
            filter_base += f" create_user_name like '%{seach_user}%'"
        if start_time and end_time:
            if filter_base:
                filter_base += " and "
            filter_base += f" create_date >= '{start_time}' and create_date <= '{end_time}'"
        if func_type:
            if filter_base:
                filter_base += " and "
            filter_base += f" func_type='{func_type}'"

        if filter_base:
            filter_base = f" where {filter_base}"
        sql_all = f"""
            select 
                convert(sum(use_quantity), decimal(10,2)) use_amount,
                sum( CASE WHEN record_type = 2 THEN 1 ELSE 0 END ) use_num,
                create_user_id, create_user_name, role_name 
            from rms_medicament_record as a 
            LEFT JOIN (
                SELECT role_name, user_id FROM rms_user
            ) b on a.create_user_id = b.user_id  
            LEFT JOIN (select medicament_id, func_type from rms_medicament
            ) as c on c.medicament_id = a.medicament_id
            {filter_base}
            order by use_amount desc
        """
        # 查询条件组合

        # 分页总数
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        if page_param:
            page_param.totalRecords = count_number
            # 数据分页
            sql_all=Utils.sql_paging_assemble(sql_all, page_param)
        data, ratio_all = self.default_data_list(sql_all, "use_amount")
        # 数据处理
        for i in data:
            try:
                i["ratio"] = Utils.classify(i["use_amount"], ratio_all)
            except Exception:
                i["ratio"] = "0%"
        return data

    # 使用频率
    def use_frequency(self, page_param, start_time, end_time, func_type,client_id=None, seach_word=None):
        filter_base1 = ""
        if client_id:
            filter_base1 += f" client_id='{client_id}' "
        if seach_word:
            if filter_base1:
                filter_base1 += "and "
            seach_word = f"%{seach_word}%"
            filter_base1 += f" (`name` like '{seach_word}' or english_name like '{seach_word}') "
        if func_type:
            if filter_base1:
                filter_base1 += "and "
            filter_base1 += f" func_type='{func_type}' "
        if filter_base1:
            filter_base1 = " where " + filter_base1
        filter_base2 = ""
        if start_time and end_time:
            filter_base2 += f" where create_date >= '{start_time}' and create_date <= '{end_time}'"

        sql_all = f"""
        select c.`name`, c.purity, c.speci, c.cas_number, c.net_weight_unit, 
			 sum( CASE WHEN c.`status` = 1 THEN 1 ELSE 0 END ) new_on,
			 c.client_id, sum(c.tp_2) sum_tp2, sum(c.tp_3) sum_tp3 from (
                select a.*, IFNULL(b.two_type, 0) tp_2, IFNULL(b.three_type, 0) tp_3 from (
                    select medicament_id, `name`, speci, purity, cas_number, net_weight_unit, `status`, client_id from rms_medicament
                    {filter_base1}
                ) a LEFT JOIN(
                    select medicament_id, 
                    sum( CASE WHEN record_type = 2 THEN 1 ELSE 0 END ) two_type,
                    sum( CASE WHEN record_type = 3 THEN 1 ELSE 0 END ) three_type
                    from rms_medicament_record {filter_base2} GROUP BY medicament_id
                )b on a.medicament_id=b.medicament_id
        ) c GROUP BY `name`, purity, speci
        """
        # 查询条件sql组合
        # 查询数据列表长度
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        if page_param:
            page_param.totalRecords = count_number
            # 处理数据
            sql_all = Utils.sql_paging_assemble(sql_all, page_param)
        data, _ = self.default_data_list(sql_all)
        return data

    def inster_log_list(self):
        import random
        data = list(self.execute("select medicament_id from rms_medicament").fetchall())
        # print(random.choice(data)[0])
        # 
        obj_list = []
        for i in range(100):
            obj = EntityMedicamentRecord(
                client_id='1c39cb24-07f8-11ed-abd4-f47b094925e1',
                client_code='12345',
                medicament_id=random.choice(data)[0],
                record_type=random.randint(1,3),
                is_empty=random.randint(0,1),
                record_remain=random.randint(1,100),
                use_quantity=float(round(random.uniform(0, 50), 2)),
                create_date=Utils.get_str_datetime(),
                create_user_id="c0858e96-d900-11eb-8209-009027e3906b",
                create_user_name="admin"
            )
            obj_list.append(obj)
        self.insert_many(obj_list)

    # #获取药剂最后一次使用余量
    # def getLastRecordRemain(self, drugId):
    #     SQL = """
    #     select RecordRemain from rms_medicamentrecord WHERE MedicamentId='""" + drugId + """' ORDER BY CreateDate DESC LIMIT 1 
    #     """
    #     try:
    #         result = BllMedicamentRecord().execute(SQL)
    #         return float(result.fetchone()[0])
    #     except Exception as e:
    #         print(e)
    #         return 0

    # #删除药剂最后一次领用记录
    # def deleteLastUseRecord(self, drugId):
    #     SQL = """
    #     delete from rms_medicamentrecord WHERE MedicamentId='""" + drugId + """' and RecordType=2  ORDER BY CreateDate DESC LIMIT 1 
    #     """
    #     result = BllMedicamentRecord().executeNoParam(SQL)
    #     return result

if __name__ == '__main__':
    aaa = BllMedicamentRecord().get_use_avg_number()
    print(aaa)
#     import random
#     from db_logic.medicament_record import BllMedicamentRecord
#     # customerId, clientId = None, '8db7e540-070f-11ed-a286-f47b094925e1'
#     # sql = f"update rms_medicament_record set use_quantity={round(random.uniform(1,10), 1)}"
#     from Common.Utils import PageParam
#     page_param = PageParam(1, 10)
#     typ_dic = BllMedicamentRecord().inster_log_list()
#     print(typ_dic)